/**
 * 
 */
package com.snapp.util.object;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.snapp.util.Bean;
import com.snapp.util.object.model.ObjectModel;
import com.snapp.util.object.model.ObjectImpl;
import com.snapp.util.service.db.DBService;
import com.snapp.util.service.model.ServiceModel;
import com.snapp.util.templates.Processor;

/**
 * 
 * 
 * @author kile
 *
 */

public class DBObject extends ObjectImpl implements Serializable {
	private static final long serialVersionUID = 3708129402442992125L;

	private static final Logger logger = Logger.getLogger(DBObject.class);
	
	private DBService service = null;
	
	private static final String TABLE_NAME = "DBObjects";
	
	// 
	private String jndiName = "java:/snapp";
	private String schemaName = "public";
	private String objectName = null;
	private String idName = "id";
	private String idSeqName = null;
	
	
	public DBObject() {
		super();
	}
	
	public Class<? extends ServiceModel> getServiceClass() {
		return DBService.class;
	}
	
	public DBService getService() {
		if (this.service == null) {
			try {
				this.service = (DBService) getServiceClass().newInstance();
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return this.service;
	}
	
	@Override
	public Vector<String> getPropertyNames() {
		if (super.getPropertyNames() == null) {
			try {
				setPropertyNames(getService().getColumnNames(jndiName, objectName));
			} catch(SQLException e) {
				logger.error("Error getting column names for '"+objectName+"'",e);
				return null;
			}
		}
		return super.getPropertyNames();
	}

	@Override
	public Vector<String> getPropertyTypes() {
		if (super.getPropertyTypes() == null) {
			try {
				setPropertyTypes(getService().getColumnTypes(jndiName, objectName));
			} catch(SQLException e) {
				logger.error("Error getting column types for '"+objectName+"'",e);
				return null;
			}
		}
		return super.getPropertyTypes();
	}

	@Override
	public String getTableName() {
		return TABLE_NAME;
	}
	
	@Override
	public void fromBean(Bean bean) {
		
		setJndiName(bean.getPropertyString("jndiname"));
		setSchemaName(bean.getPropertyString("schemaname"));
		setObjectName(bean.getPropertyString("objectname"));
		setIdSeqName(bean.getPropertyString("seqname"));
		setIdName(bean.getPropertyString("idname"));
		
		super.fromBean(bean);
	}
	
	// get the identifier as defined by the "idName"
	public String getId() {
		return getBean().getPropertyString(getIdName());
	}
	/**
	 * Get a copy of this object
	 * @return
	 */
	public ObjectModel copy() {
		ObjectModel snob = duplicateInstance();
		snob.setBean(getBean());
		return snob;
	}
	
	/**
	 * Duplicate the object without bean copy
	 * @return
	 */
	public ObjectModel duplicateInstance() {
		DBObject snob = new DBObject();
		snob.setSNAppGlobalName(getSNAppGlobalName());
		snob.setReadOnly(isReadOnly());
		snob.setPropertyNames(getPropertyNames());
		
		snob.setJndiName(jndiName);
		snob.setSchemaName(schemaName);
		snob.setObjectName(objectName);
		snob.setIdSeqName(idSeqName);
		snob.setIdName(getIdName());
		return snob;
	}
	

	/**
	 * Get a list of DBObjects based on the given filter
	 * @param filter
	 * @return
	 */
	public Vector<ObjectModel> list(String filter, HashMap<String, String> params) {
		String distinct = "*";
		if (params != null) {
			if (params.containsKey("distinctResults")) distinct = "distinct "+params.get("distinctResults");
		}
		
		Vector<ObjectModel> list = new Vector<ObjectModel>();
		try {
			String sql = "Select "+distinct+" from "+schemaName+"."+objectName;
			if (filter != null && filter.length() > 0) {
				if (filter.indexOf("where") == -1 && filter.length() > 0) sql += " where ";
				sql += filter;
			}
			
			Vector<Bean> beans = getService().listBeans(jndiName, sql);
			Iterator<Bean> itr = beans.iterator();
			while (itr.hasNext()) {
				ObjectModel snob = duplicateInstance();
				snob.setBean(itr.next());
				list.add(snob);
					
			}
		} catch(Exception e) {
			setResultCode(RESULT_CODE_ERROR);
			setResultMessage("Error loading db object list");
			logger.error("Error loading db object list", e);
		}
		return list;
	}
	
	public int count(String filter, HashMap<String, String> params) {
		int count = 0;
		try {
			String distinct = "*";
			if (params != null) {
				if (params.containsKey("distinctResults")) distinct = "distinct "+params.get("distinctResults");
			}
			
			String sql = "Select count(*) from(select "+distinct+" from "+schemaName+"."+objectName;
			if (filter.indexOf("where") == -1 && filter.length() > 0) sql += " where ";
			sql += filter + ") as count";
			
			// execute count query
			count = getService().simpleExecute(jndiName, sql);
			
		} catch(Exception e) {
			setResultCode(RESULT_CODE_ERROR);
			setResultMessage("Error counting db object list");
			logger.error("Error counting db object list", e);
		}
		return count;
	}
	
	/**
	 * Load a single object based on the identifier
	 * @param id
	 */
	public void delete(String filter) {
		if (!isReadOnly()) {
			try {
				String sql = "delete from "+schemaName+"."+objectName;
				if (filter.indexOf("where") == -1 && filter.length() > 0) sql += " where ";
				sql += filter;
				
				getService().simpleExecute(jndiName, sql);
			} catch(Exception e) {
				logger.error("Error deleting db objects", e);
			}
		} else {
			setResultCode(RESULT_CODE_WARNING);
			setResultMessage("Attempting to delete a read-only object!");
			logger.warn("Attempting to delete a read-only object!");
		}
	}

	/**
	 * Load a single object based on the identifier
	 * @param id
	 */
	public void load(String filter) {
		try {
			String sql = "Select * from "+schemaName+"."+objectName;
			if (filter.indexOf("where") == -1 && filter.length() > 0) sql += " where ";
			sql += filter;
			
			setBean(getService().loadBean(jndiName, sql));
			
		} catch (Exception e) {
			setResultCode(RESULT_CODE_ERROR);
			setResultMessage("Error loading db object");
			logger.error("Error loading db object", e);
		}
	}

	/**
	 * Save a copy of this bean..
	 */	
	public void save(HashMap<String, Object> saveParams) {
		if (!isReadOnly()) {
			try {
				// populate the bean
				if (saveParams != null) getBean().populate(saveParams);
				
				HashMap<Integer, Object> oVals = new HashMap<Integer, Object>();
				int ndx = 0;
				
				String sql;
				if (getBean().getProperty(getIdName()) == null || Integer.parseInt(getBean().getPropertyString(getIdName())) < 1) {
					// get next id
					sql = "Select nextVal('"+idSeqName+"');";
					Integer key = (Integer)getService().simpleExecute(jndiName, sql);
					getBean().setProperty(getIdName(), key, key.toString());
	
					String cols = "";
					String vals = "";
					
					// build insert sql
					Iterator<String> itr = getBean().getPropertyNames().iterator();
					while(itr.hasNext()) {
						String nxt = itr.next();
						if (getPropertyNames().contains(nxt)) {
							
							if (cols.length() > 0) cols += ",";
							
							// append to columns
							cols += "\""+nxt+"\"";
							
							if (vals.length() > 0) vals += ",";
							
							Object obj = getBean().getProperty(nxt);
							if (DBService.HANDLER_MAPPINGS.containsKey(obj.getClass())) {
								ndx++;
								oVals.put(ndx, obj);
								vals += "?";
								
							} else {
								vals += "'"+getService().cleanSqlIn(getBean().getPropertyString(nxt))+"'";
							}
							
						}
					}
					// set insert 
					sql = "Insert Into "+schemaName+"."+objectName+"("+cols+") values("+vals+");";
					
				} else {
					// set update
					sql = "Update "+schemaName+"."+objectName+" set ";
					String vals = "";
					
					Iterator<String> itr = getBean().getPropertyNames().iterator();
					while(itr.hasNext()) {
						String nm = itr.next();
						if (!nm.equals(getIdName()) && getPropertyNames().contains(nm)) {
							if (vals.length() > 0) vals += ",";
							
							vals += "\""+nm+"\" = ";

							Object obj = getBean().getProperty(nm);
							if (DBService.HANDLER_MAPPINGS.containsKey(obj.getClass())) {
								ndx++;
								oVals.put(ndx, obj);
								vals += "?";
							} else {
								vals += "'"+getService().cleanSqlIn(getBean().getPropertyString(nm))+"'";
							}
							
						}
					}	
					
					sql += vals;
					sql += " where "+getIdName()+" = "+getBean().getPropertyString(getIdName())+";";
				}
				// execute query
				logger.info("SQL:  "+sql);
				getService().execute(jndiName, sql, oVals);	
				
			} catch (SQLException se) {
				logger.error("Error saving DBObject", se);
				setResultCode(RESULT_CODE_ERROR);
				setResultMessage("Error saving DBObject");
			}
			
		} else {
			logger.warn("Attempting to save a read-only object!");
		}
	}

	/**
	 * Save a list based on the given parameters.
	 * 
	 * This method looks for the given delimiter in each parameter and invokes a "save" for each
	 * 
	 * @param params
	 * @param delimiter
	 */
	public void saveList(HashMap<String, Object> params, String delimiter) {
		HashMap<String,String[]> splitVals = new HashMap<String, String[]>();
		Iterator<String> itr = params.keySet().iterator();
		while (itr.hasNext()) {
			String key = itr.next();
			Object nxt = params.get(key);
			String[] list;
			if (nxt.getClass().equals(String.class)) {
				String snxt = (String)nxt;
				if (snxt.indexOf(delimiter) > -1) {
					list = snxt.split(delimiter);
					splitVals.put(key, list);
				}
			}
		}
		
		if (splitVals.size() > 0) {
			String[] list = splitVals.get(splitVals.keySet().toArray()[0]);
			for (int i=0; i < list.length; i++) {
				HashMap<String, Object> pCopy = new HashMap<String, Object>();
				
				Iterator<String> it = params.keySet().iterator();
				while(it.hasNext()) {
					String ky = it.next();
					if (splitVals.containsKey(ky)) {
						pCopy.put(ky, splitVals.get(ky)[i]);
					} else {
						pCopy.put(ky, params.get(ky));
					}
				}
				
				// copy and save instance
				ObjectModel copy = duplicateInstance();
				copy.save(pCopy);
			}
		} else {
			save(params);
		}
	}


	/**
	 * Convert a parameter map to sql filter
	 * @param filterParams
	 * @return
	 */
	public String filterParamsToString(HashMap<String, String> filterParams) {
		String filter = "";
		String ffilter = null;
		if (filterParams != null && filterParams.size() > 0) {
			
			if (filterParams.containsKey("sqlFilter") || filterParams.containsKey("sqlfilter")) {
				String fltr = filterParams.containsKey("sqlFilter") ? filterParams.get("sqlFilter") : filterParams.get("sqlfilter");
				filter += fltr;
			}
			
			// build parameters from map
			Iterator<String> i = filterParams.keySet().iterator();
			while (i.hasNext()) {
				String key = i.next();
				// if it is a table column, append it
				if (getPropertyNames().contains(key)) {
					if (filter.length() > 0) filter += " and ";
					filter += " \""+key+"\"='"+getService().cleanSqlIn(filterParams.get(key))+"'";
				} else {
					/*
					 * Check for operation nesting in key
					 */
					String[] keyArr = key.split("::");
					if (keyArr.length > 1) {
						String op = keyArr[1];
						filter += " \""+key+"\" " + op + " '"+getService().cleanSqlIn(filterParams.get(key))+"'";
					}
				}
			}
			
			String orderBy = null;
			if (filterParams.containsKey("orderBy")) {
				orderBy = " order by " + filterParams.get("orderBy");
			}
	
			String groupBy = null;
			String having = null;
			if (filterParams.containsKey("groupBy")) {
				groupBy = " group by " + filterParams.get("groupBy");
				if (filterParams.containsKey("having")) {
					having = " having " + filterParams.get("having");
				}
			}
	
			String limit = null;
			if (filterParams.containsKey("limitResults")) {
				limit = " limit " + filterParams.get("limitResults");
			}
	
			String offset = null;
			if (filterParams.containsKey("offsetResults")) {
				offset = " offset " + filterParams.get("offsetResults");
			}
			
			// append more filters
			filter += (orderBy != null) ? orderBy : "";
			filter += (groupBy != null) ? groupBy : "";
			filter += (having != null) ? having : "";
			filter += (limit != null) ? limit : "";
			filter += (offset != null) ? offset : "";
			
			ffilter = " where "+filter;
		}
		
		return ffilter;
	}
	

	public void setJndiName(String jndiName) {
		this.jndiName = jndiName;
	}

	public String getJndiName() {
		return jndiName;
	}

	public void setObjectName(String name) {
		this.objectName = name;
	}
	public String getObjectName() {
		return objectName;
	}
	
	public void setIdName(String idName) {
		this.idName = idName;
	}

	public String getIdName() {
		return idName;
	}

	public void setIdSeqName(String seqName) {
		this.idSeqName = seqName;
	}

	public String getIdSeqName() {
		return idSeqName;
	}

	/**
	 * @param schemaName the schemaName to set
	 */
	public void setSchemaName(String schemaName) {
		this.schemaName = schemaName;
	}

	/**
	 * @return the schemaName
	 */
	public String getSchemaName() {
		return schemaName;
	}

}

